package tabletask

import (
	"crypto/sha1"
	"encoding/hex"
	"errors"
	"fmt"
	"io"
	"io/ioutil"
	"os"
	"strconv"
	"time"

	"github.com/astaxie/beego/logs"
	"github.com/astaxie/beego/orm"
	"github.com/xuri/excelize/v2"

	"cvevulner/cve-timed-task/db_models"
	"cvevulner/cve-timed-task/downloadfiletask"
	"cvevulner/cve-timed-task/util"
	"cvevulner/models"
)

// HandleDate CVSS official website data crawling data storage database
func HandleDate() {
	ormModel := orm.NewOrm()
	filePath, err := downloadfiletask.HandleOne()
	if err != nil {
		logs.Error("downloadfiletask.HandleOne error:", err)
		return
	}

	files, err := ioutil.ReadDir(filePath)
	if err != nil {
		logs.Error(" ioutil.ReadDir error: ", err, filePath)
		return
	}
	for _, file := range files {
		fileName := file.Name()
		if file.IsDir() {
			logs.Warn(filePath+fileName, "is Dir, remove")
			_ = os.RemoveAll(filePath + fileName)
			continue
		}

		f, err := os.Open(filePath + fileName)
		if err != nil {
			continue
		}
		hash := sha1.New()
		_, err = io.Copy(hash, f)
		if err != nil {
			logs.Error("io.Copy :", err)
			_ = f.Close()
			continue
		}
		_ = f.Close()
		sum := hash.Sum(nil)
		hashValue := hex.EncodeToString(sum)
		logs.Info("open transaction")
		tranErr := ormModel.Begin()
		if tranErr != nil {
			logs.Error(" Open transaction error:", tranErr.Error())
			continue
		}
		list, rowsAffected, err := db_models.SelectCveFileHashByFileName(fileName, ormModel)
		if err != nil {
			logs.Error("db_models.SelectCveFileHashByFileName :", err)
			tranErr = ormModel.Rollback()
			if tranErr != nil {
				logs.Error(" Rollback transaction error:", tranErr.Error())
			}
			continue
		}
		fmt.Println(hashValue)
		if rowsAffected > 0 && util.InSlice(list, hashValue) {
			logs.Info("file has been parsed", fileName)
			tranErr = ormModel.Rollback()
			if tranErr != nil {
				logs.Error(" Rollback transaction error:", tranErr.Error())
			}
			_ = os.Remove(filePath + fileName)
			continue
		}
		err = db_models.InsertCveFileHash(fileName, hashValue, ormModel)
		if err != nil {
			logs.Error("db_models.InsertCveFileHash :", err)
			tranErr = ormModel.Rollback()
			if tranErr != nil {
				logs.Error(" Rollback transaction error:", tranErr.Error())
			}
			continue
		}
		tranErr = ormModel.Commit()
		if tranErr != nil {
			logs.Error(" Commit transaction error:", tranErr.Error())
			continue
		}
		excel, err := excelize.OpenFile(filePath + fileName)
		if err != nil {
			logs.Error("excelize.OpenReader:", err)
			continue
		}

		rows, err := excel.GetRows("Sheet1")
		if err != nil {
			logs.Error("excel.GetRows Sheet1 error", err)
			continue
		}

		var packName string
		var cveVersion string
		var cveNum string
		now := time.Now().Format("2006-01-02 15:04:05")
		for _, row := range rows[1:] {
			if len(row) < 1 {
				continue
			}
			packName = row[0]
			if len(row) > 1 {
				cveVersion = row[1]
				if len(row) > 2 {
					cveNum = row[2]
				} else {
					cveNum = ""
				}
			} else {
				cveVersion = ""
			}

			tranErr := ormModel.Begin()
			if tranErr != nil {
				logs.Error(" Open transaction error:", tranErr.Error())
				continue
			}
			resultDict, err := db_models.GetCveOriginExcelTypeTwo(cveNum, packName, cveVersion, ormModel)
			if err != nil {
				if errors.Is(orm.ErrNoRows, err) {
					url := fmt.Sprintf("https://nvd.nist.gov/vuln/detail/%s", cveNum)
					crawlList, err := Crawling(url)
					if err != nil {
						logs.Error("Crawling error:", err)
						tranErr = ormModel.Rollback()
						if tranErr != nil {
							logs.Error("Rollback transaction error:", tranErr.Error())
						}
						continue
					}
					rep := GetRepair(cveNum)
					if len(rep) > 0 {
						crawlList.RepairTime = rep
					}
					nvdScore, err := strconv.ParseFloat(crawlList.NvdScore, 64)
					if err != nil {
						logs.Error(err.Error())
					}
					err = db_models.InsertCveOriginExcel(&models.OriginExcel{
						CveNum:            cveNum,
						CveUrl:            url,
						CveVersion:        cveVersion,
						PackName:          packName,
						ScoreType:         crawlList.ScoreType,
						NVDScore:          nvdScore,
						CveLevel:          crawlList.CveLevel,
						CveDesc:           crawlList.CveDesc,
						RepairTime:        crawlList.RepairTime,
						NVDVector:         crawlList.VectorValue,
						AttackVector:      crawlList.AttackVector,
						AccessVector:      crawlList.AccessVector,
						AttackComplexity:  crawlList.AttackComplexity,
						AccessComplexity:  crawlList.AccessComplexity,
						PrivilegeRequired: crawlList.PrivilegeRequired,
						UserInteraction:   crawlList.UserInteraction,
						Scope:             crawlList.Scope,
						Confidentiality:   crawlList.Confidentiality,
						Integrity:         crawlList.Integrity,
						Availability:      crawlList.Availability,
						Authentication:    crawlList.Authentication,
						CveStatus:         0,
						CreateTime:        now,
						UpdateTime:        now,
					}, ormModel)
					if err != nil {
						logs.Error("db_models.InsertCveOriginExcel error:", err)
						tranErr = ormModel.Rollback()
						if tranErr != nil {
							logs.Error("Rollback transaction error:", tranErr.Error())
						}
						continue
					}

				} else {
					logs.Error("db_models.GetCveOriginExcelTypeTwo error:", err)
					tranErr = ormModel.Rollback()
					if tranErr != nil {
						logs.Error("Rollback transaction error:", tranErr.Error())
					}
					continue
				}
			} else {
				url := fmt.Sprintf("https://nvd.nist.gov/vuln/detail/%s", cveNum)
				crawlList, err := Crawling(url)
				if err != nil {
					logs.Error("Crawling error:", err)
					tranErr = ormModel.Rollback()
					if tranErr != nil {
						logs.Error("Rollback transaction error:", tranErr.Error())
					}
					continue
				}
				rep := GetRepair(cveNum)
				if len(rep) > 0 {
					crawlList.RepairTime = rep
				}
				if crawlList.NvdScore != "" || crawlList.CveDesc != "" {
					nvdScore, err := strconv.ParseFloat(crawlList.NvdScore, 64)
					if err != nil {
						logs.Error(err.Error())
					}
					if crawlList.CveDesc != "" && resultDict.NVDScore == nvdScore && resultDict.NVDVector == crawlList.VectorValue {
						comp := make([]interface{}, 0, 10)
						comp = append(comp, 3, 4, 7)
						if util.InSlice(comp, int(resultDict.CveStatus)) {
							logs.Info("update data aa:", cveNum)
							err = db_models.UpdateCveOriginExcelTypeThree(models.OriginExcel{
								NVDScore:          nvdScore,
								CveLevel:          crawlList.CveLevel,
								CveDesc:           crawlList.CveDesc,
								RepairTime:        crawlList.RepairTime,
								NVDVector:         crawlList.VectorValue,
								AttackVector:      crawlList.AttackVector,
								AccessVector:      crawlList.AccessVector,
								AttackComplexity:  crawlList.AttackComplexity,
								AccessComplexity:  crawlList.AccessComplexity,
								PrivilegeRequired: crawlList.PrivilegeRequired,
								UserInteraction:   crawlList.UserInteraction,
								Scope:             crawlList.Scope,
								Confidentiality:   crawlList.Confidentiality,
								Integrity:         crawlList.Integrity,
								Availability:      crawlList.Availability,
								Authentication:    crawlList.Authentication,
								CveStatus:         1,
								UpdateTime:        now,
								CveNum:            cveNum,
								PackName:          packName,
								CveVersion:        cveVersion,
							}, ormModel)
							if err != nil {
								logs.Error("db_models.UpdateCveOriginExcelTypeThree error:", err)
								tranErr = ormModel.Rollback()
								if tranErr != nil {
									logs.Error("Rollback transaction error:", tranErr.Error())
								}
								continue
							}
						} else {
							logs.Info("The database is the latest data:", cveNum)
						}
					} else {
						logs.Info("update data dd:", cveNum)
						err = db_models.UpdateCveOriginExcelTypeFour(models.OriginExcel{
							NVDScore:          nvdScore,
							CveLevel:          crawlList.CveLevel,
							CveDesc:           crawlList.CveDesc,
							RepairTime:        crawlList.RepairTime,
							NVDVector:         crawlList.VectorValue,
							AttackVector:      crawlList.AttackVector,
							AccessVector:      crawlList.AccessVector,
							AttackComplexity:  crawlList.AttackComplexity,
							AccessComplexity:  crawlList.AccessComplexity,
							PrivilegeRequired: crawlList.PrivilegeRequired,
							UserInteraction:   crawlList.UserInteraction,
							Scope:             crawlList.Scope,
							Confidentiality:   crawlList.Confidentiality,
							Integrity:         crawlList.Integrity,
							Availability:      crawlList.Availability,
							Authentication:    crawlList.Authentication,
							CveStatus:         1,
							UpdateTime:        now,
							ScoreType:         crawlList.ScoreType,
							CveNum:            cveNum,
							PackName:          packName,
							CveVersion:        cveVersion,
						}, ormModel)
						if err != nil {
							logs.Error("db_models.UpdateCveOriginExcelTypeFour error:", err)
							tranErr = ormModel.Rollback()
							if tranErr != nil {
								logs.Error("Rollback transaction error:", tranErr.Error())
							}
							continue
						}
					}
				} else {
					logs.Error("error:", resultDict)
				}
			}

			specError, err := db_models.GetCveSpecError(cveNum, ormModel)
			if err != nil {
				if !errors.Is(orm.ErrNoRows, err) {
					logs.Error("db_models.GetCveSpecError error:", err)
				}
			} else {
				err = db_models.UpdateCveOriginExcelTypeFive(models.OriginExcel{
					CveDesc:   specError.Description,
					CveStatus: 6,
					CveNum:    cveNum,
				}, ormModel)
				if err != nil {
					logs.Error("db_models.UpdateCveOriginExcelTypeFive error:", err)
					tranErr = ormModel.Rollback()
					if tranErr != nil {
						logs.Error("Rollback transaction error:", tranErr.Error())
					}
					continue
				}
			}
			tranErr = ormModel.Commit()
			if tranErr != nil {
				logs.Error(" Commit transaction error:", tranErr.Error())
				continue
			}
		}
	}
}
